package oop.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

import oop.data.Test;
import oop.data.Topic;
import oop.db.Database;
import oop.util.Utils;

public class TestDAO {

	public static Test fetchById(long id) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "SELECT test_id, test_name, test_description, "
					+ "test_create_date, test_topic, "
					+ "test_author, test_type, test_time "
					+ "FROM tblTest "
					+ "WHERE test_id = ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, id);
			return Utils.single(fromStatement(stmt));
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static List<Test> fetchByName(String name, int limit) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "SELECT test_id, test_name, test_description, "
					+ "test_create_date, test_topic, "
					+ "test_author, test_type, test_time "
					+ "FROM tblTest "
					+ "WHERE test_name LIKE ? "
					+ "ORDER BY test_name ASC "
					+ "LIMIT ?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			stmt.setLong(2, limit);
			return fromStatement(stmt);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static long count() throws SQLException {
		String sql = "SELECT COUNT(*) FROM tblTest";
		return Database.get().count(sql);
	}
	
	public static List<Test> fetch(int start, int length) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT test_id, test_name, test_description, "
				+ "test_create_date, test_topic, "
				+ "test_author, test_type, test_time "
				+ "FROM tblTest "
				+ "ORDER BY test_name ASC " 
				+ "LIMIT ?, ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, start);
			stmt.setInt(2, length);
			rs = stmt.executeQuery();
			
			List<Test> tests = new ArrayList<Test>();
			while (rs.next()) {
				tests.add(fromRow(rs));
			}
			return tests;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}
	
	public static List<Test> fetchCreateDateOrder(int start, int length) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT test_id, test_name, test_description, "
						+ "test_create_date, test_topic, "
						+ "test_author, test_type, test_time "
					+ "FROM tblTest "
					+ "ORDER BY test_create_date DESC " 
					+ "LIMIT ?, ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, start);
			stmt.setInt(2, length);
			rs = stmt.executeQuery();
			
			List<Test> tests = new ArrayList<Test>();
			while (rs.next()) {
				tests.add(fromRow(rs));
			}
			return tests;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static long countByStudent(int id) throws SQLException {
		String sql = "SELECT COUNT(*) " +
				"FROM tblTest, tblUserTest " +
				"WHERE tblTest.test_id = tblUserTest.test_id";
		return Database.get().count(sql);
	}
	
	public static List<Test> fetchByStudent(int id, int start, int length) throws SQLException {
		String sql = "SELECT test_id, test_name, test_description, "
						+ "test_create_date, test_topic, "
						+ "test_author, test_type, test_time "
				+ "FROM  tblTest, tblUserTest " 
				+ "WHERE test_author = tblUser.user_id " 
				+ " AND tblUser.user_id = " + id
				+ " tblTest.test_id = tblUserTest.test_id "
				+ "LIMIT " + start + ", " + length;
		return fetch(sql);
	}
	
	public static long countByTopic(long id) throws SQLException {
		String sql = "SELECT COUNT(*) " +
				"FROM tblTest " +
				"WHERE test_topic = " + id;
		return Database.get().count(sql);
	}

	public static List<Test> fetchByTopic(long topicId, int start, int length) throws SQLException {
		Topic topic = TopicDAO.fetchById(topicId);
		Set<Topic> descendants = topic.getDescendants();
		StringBuilder sb = new StringBuilder();
		for (Topic descendant : descendants) {
			sb.append(descendant.getId()).append(", ");
		}
		if (sb.length() > 0) {
			sb.delete(sb.length()-2, sb.length());
		}
		String descendantsStr = sb.toString(); 
		
		String sql = "SELECT test_id, test_name, test_description, "
						+ "test_create_date, test_topic, "
						+ "test_author, test_type, test_time "
				+ "FROM  tblTest, tblTestTopic " 
				+ "WHERE test_topic = top_id "
				+ "AND top_id IN (" + descendantsStr + ") "
				+ "LIMIT " + start + ", " + length;
		return fetch(sql);
	}
	
	public static long countByAuthor(long id) throws SQLException {
		String sql = "SELECT COUNT(*) " +
				"FROM tblTest " +
				"WHERE test_author= " + id;
		return Database.get().count(sql);
	}

	public static List<Test> fetchByAuthor(long id, int start, int length) throws SQLException {
		String sql = "SELECT test_id, test_name, test_description, "
				+ "test_create_date, test_topic,  "
				+ "test_author, test_type, test_time "
				+ "FROM  tblTest, tblUser " 
				+ "WHERE test_author = user_id "
				+ "AND test_author = " + id + " "
				+ "LIMIT " + start + ", " + length;
		return fetch(sql);
	}

	private static List<Test> fetch(String sql) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
	
			List<Test> tests = new ArrayList<Test>();
			while (rs.next()) {
				tests.add(fromRow(rs));
			}
			return tests;
		} finally {
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	private static Test fromRow(ResultSet rs) throws SQLException {
		return new Test(rs.getLong(1), rs.getString(2), rs.getString(3), rs
				.getTimestamp(4), rs.getLong(5), rs.getLong(6), rs.getString(7), rs
				.getInt(8));
	}

	public static void persist(Test test) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "UPDATE tblTest " +
				"SET test_name=?, test_description=?" +
				", test_type=?, test_time=? " +
				"WHERE test_id=?";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, test.getName());
			stmt.setString(2, test.getDescription());
			stmt.setString(3, test.getType());
			stmt.setInt(4, test.getTime());
			stmt.setLong(5, test.getId());
			stmt.executeUpdate();
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static Test create(String name, String description, long topicId,
			long authorId, String type, int time) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		try {
			String sql = "INSERT INTO tblTest (test_name, test_description, " +
					"test_create_date, test_author, test_topic, test_type, test_time) " +
					"VALUES (?, ?, ?, ?, ?, ?, ?)";
			
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);
			stmt.setString(2, description);
			Timestamp createDate = new Timestamp(new Date().getTime());
			stmt.setTimestamp(3, createDate);
			stmt.setLong(4, authorId);
			stmt.setLong(5, topicId);
			stmt.setString(6, type);
			stmt.setInt(7, time);
			stmt.executeUpdate();
			
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			long id = rs.getLong(1);

			return new Test(id, name, description, createDate, topicId,
					authorId, type, time);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}
	
	public static int drop(long id) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			String sql = "DELETE FROM tblTest WHERE test_id = " + id;
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}
	
	private static List<Test> fromStatement(PreparedStatement stmt)
			throws SQLException {
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery();
			List<Test> tests = new LinkedList<Test>();
			while (rs.next()) {
				tests.add(new Test(rs.getLong(1), rs.getString(2), rs
						.getString(3), rs.getTimestamp(4), rs.getLong(5), rs
						.getLong(6), rs.getString(7), rs.getInt(8)));
			}
			return tests;
		} finally {
			if (rs != null)
				rs.close();
		}
	}

}
